/*

EXEC sp_creadit_debit_note_report_proc '0','0','','0','0','0','XLREPOR1T','0'
*/
ALTER PROC sp_creadit_debit_note_report_proc

@CUSTOMERIDS	VARCHAR(200),
@PRODUCTIDS	VARCHAR(200),
@LITYPE		VARCHAR(50),
@STATUS		VARCHAR(50),
@FROMDATE	VARCHAR(50),
@TODATE		VARCHAR(50),
@REPORTTYPE	VARCHAR(50),
@DEFAULTPROMPT	VARCHAR(50)

AS
BEGIN
	
	CREATE TABLE #CREADIT_DEBIT_TABLE  
			( CUSTOMER VARCHAR(65),[END CUSTOMER] VARCHAR(65), [END USER] VARCHAR(65),[SALES ORDER #] VARCHAR(50),
			  [LINT ITEM #]	VARCHAR(50), [PACKING SLIP #] VARCHAR(50), [SHIP DATE] DATETIME, [ISSUE DATE] DATETIME,
			  [CHANGE QTY] INT, [CHANGE PRICE] FLOAT, [CHANGE AMOUNT] FLOAT, [ADJUST UNITS] INT, 
			  [CREDIT/DEBIT #] VARCHAR(50), [CREADIT STATUS] VARCHAR(50),[IS CREADIT NOTE] BIT, [IS UNIT] BIT,
			  [PRODUCT NAME] VARCHAR(50), [REFERENCE NUMBER] VARCHAR(50) )

	DECLARE @CREADITSTRING 	VARCHAR(4000)

	SET @CREADITSTRING = N'
	INSERT INTO #CREADIT_DEBIT_TABLE
			( [CREDIT/DEBIT #],[ISSUE DATE],[IS CREADIT NOTE],[IS UNIT],[LINT ITEM #],[CHANGE PRICE],
		      	  [CHANGE AMOUNT],[CREADIT STATUS],[CHANGE QTY], [SALES ORDER #],CUSTOMER,[END CUSTOMER],
		          [END USER],[PACKING SLIP #],[SHIP DATE],[ADJUST UNITS],[PRODUCT NAME], [REFERENCE NUMBER])
	SELECT 
		CREDITNOTE_NO,ISSUE_DATE,IS_CREDIT_NOTE,IS_UNITS,
		COOL_LINE_ITEM_NO,
		DISCOUNT_PER_UNIT,DISCOUNT_AMOUNT,CNLIT.STATUS,
		ADJUSTMENT_QTY,SALESORDER_NO,C_NAME,
		( CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0  ) 
			THEN ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.Customer_ID )
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
	 	 END ),
		( CASE WHEN ( SO.SO_ENDCUSTOMER_ID IS NULL OR SO.SO_ENDCUSTOMER_ID <= 0  ) 
				THEN (CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0 ) 
					THEN ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.Customer_ID )
					ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID =  SO.SO_ODM_ID )
					END) 
				ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID)
		END ),
		LABEL,CST_SHIP_DATE,
		( CASE WHEN IS_UNITS = 0 THEN ADJUSTMENT_QTY ELSE NULL END ),
		 PART_NUMBER, CST.INV_No
	FROM
		CREDIT_NOTE_TABLE CNT INNER JOIN CREDIT_NOTE_LINE_ITEM_TABLE CNLIT ON CNT.CREDITNOTE_ID = CNLIT.CN_ID
		INNER JOIN SALES_ORDER SO ON CNT.SALESORDER_ID = SO.SALESORDER_ID
		INNER JOIN CUSTOMER C ON SO.CUSTOMER_ID = C.CUSTOMER_ID
		INNER JOIN CUSTOMER_SHIPPING_TABLE CST ON CNT.CUSTOMERSHIPPING_ID = CST.CUSTOMERSHIPPING_ID
		LEFT OUTER JOIN CUSTOMER_OPEN_ORDER_LINEITEM COOL ON CNLIT.LINEITEM_ID = COOL.LINEITEM_ID
		INNER JOIN PRODUCT P ON COOL.PRODUCT_ID = P.PRODUCT_ID 
	WHERE 	
		CST_STATUS = ''SHIPPED'' '
		
	IF @CUSTOMERIDS IS NOT NULL AND @CUSTOMERIDS <> '0'
	BEGIN
		SET @CREADITSTRING = @CREADITSTRING + ' AND SO.CUSTOMER_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @CUSTOMERIDS +'''))'
	END
	
	IF @PRODUCTIDS IS NOT NULL AND @PRODUCTIDS <> '0'
	BEGIN
		SET @CREADITSTRING = @CREADITSTRING + ' AND COOL.PRODUCT_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @PRODUCTIDS +'''))'
	END

	IF @LITYPE IS NOT NULL AND LEN(LTRIM(RTRIM(@LITYPE))) > 0
	BEGIN
		SET @CREADITSTRING = @CREADITSTRING + ' AND IS_CREDIT_NOTE = '''+ @LITYPE +''''
	END
	ELSE
	BEGIN
		SET @CREADITSTRING = @CREADITSTRING + ' AND ( IS_CREDIT_NOTE = 0 OR IS_CREDIT_NOTE = 1 ) '
	END

	IF @STATUS IS NOT NULL AND @STATUS <> '0'
	BEGIN
		SET @CREADITSTRING = @CREADITSTRING + ' AND CNLIT.STATUS = '''+ @STATUS +''''
	END

	IF ( @FROMDATE IS NOT NULL AND @FROMDATE <> '0' )
	BEGIN
		SET @CREADITSTRING = @CREADITSTRING +' AND ISSUE_DATE >= '''+ CONVERT(VARCHAR,@FROMDATE, 101)+ ' 12:00AM ' +''''
	END

	IF ( @TODATE IS NOT NULL AND @TODATE <> '0' )
	BEGIN
		SET @CREADITSTRING = @CREADITSTRING + +' AND ISSUE_DATE <= '''+ CONVERT(VARCHAR,@TODATE, 101)+ ' 11:59PM ' +''''
	END

	--PRINT '@CREADITSTRING ='+@CREADITSTRING
	EXEC (@CREADITSTRING)

	IF @REPORTTYPE = 'XLREPORT'
	BEGIN
		SELECT
			  CUSTOMER,[END CUSTOMER],[END USER],[CREDIT/DEBIT #],[REFERENCE NUMBER],[SALES ORDER #],[LINT ITEM #],[PACKING SLIP #],[PRODUCT NAME],
			  [SHIP DATE],[ISSUE DATE],[CHANGE QTY],[CHANGE PRICE],[CHANGE AMOUNT],[CREADIT STATUS]			  
		FROM 
			#CREADIT_DEBIT_TABLE
		ORDER BY
			[SALES ORDER #],[LINT ITEM #],[PACKING SLIP #],[ISSUE DATE] DESC
	END
	ELSE
	BEGIN
		SELECT
		 	CUSTOMER,[END CUSTOMER],[END USER],[SALES ORDER #],[LINT ITEM #],[PACKING SLIP #],[PRODUCT NAME],
			[SHIP DATE],[ISSUE DATE],[CHANGE QTY],[CHANGE PRICE],[CHANGE AMOUNT],[ADJUST UNITS],
			[CREDIT/DEBIT #],[IS CREADIT NOTE],[IS UNIT],[CREADIT STATUS],[REFERENCE NUMBER]
		FROM 
			#CREADIT_DEBIT_TABLE
		ORDER BY
			[SALES ORDER #],[LINT ITEM #],[PACKING SLIP #],[ISSUE DATE] DESC
	END

END